using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DLL.Essences;
using System.Configuration;

namespace DLL.DAL
{
    class PurchDocumentDAL
    {
        public static DataTable GetAllPurchDocument()
        {
            DataTable dt = new DataTable();
            OleDbConnection connect = new OleDbConnection(ConfigurationManager.ConnectionStrings["base"].ConnectionString);
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "select PurchDocument.ID,"+
                                  "PurchDocument.ProvidersDocumentNumber,"+
                                  "PurchDocument.Number,"+
                                  "PurchDocument.DocumentDate,"+
                                  "PurchDocument.RegDate,"+
                                  "PriceCondition.Name,"+
                                  "Providers.Name,"+
                                  "PurchDocument.CustSumm" +
                                  "PurchDocument.PurchSumm" +
                                  "PurchDocument.EndSumm" +
                                  "PurchDocument.Date" +
                                  "Users.UserName" +
                                  "from users,providers,PriceCondition,PurchDocument where users.id=PurchDocument.UserID and providers.id=PurchDocument.providersID and pricecondition.id=PurchDocument.priceconditionid";
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dt);
            return dt;
        }

        public static PurchDocument GetPurchDocumentByID(int id)
        {
            DataTable dt = new DataTable();
            OleDbConnection connect = new OleDbConnection(ConfigurationManager.ConnectionStrings["base"].ConnectionString);
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "select PurchDocument.ID," +
                                  "PurchDocument.ProvidersDocumentNumber," +
                                  "PurchDocument.Number," +
                                  "PurchDocument.DocumentDate," +
                                  "PurchDocument.RegDate," +
                                  "PurchDocument.PriceConditionID," +
                                  "PurchDocument.ProvidersID," +
                                  "PurchDocument.CustSumm" +
                                  "PurchDocument.PurchSumm" +
                                  "PurchDocument.EndSumm" +
                                  "PurchDocument.Date" +
                                  "PurchDocument.UsersID" +
                                  "from PurchDocument where id=@id";
            command.Parameters.AddWithValue("@id", id);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dt);
            DataTableReader dr = dt.CreateDataReader();
            PurchDocument pd=null;
            if (dr.Read())
            {
                pd.Id = Convert.ToInt32(dr.GetValue(0));
                pd.ProvidersDocumentNumber = dr.GetValue(1).ToString();
                pd.Number = Convert.ToInt32(dr.GetValue(2));
                pd.DocumentDate = Convert.ToDateTime(dr.GetValue(3));
                pd.RegDate = Convert.ToDateTime(dr.GetValue(4));
                pd.PriceConditionID = Convert.ToInt32(dr.GetValue(5));
                pd.ProvidersID = Convert.ToInt32(dr.GetValue(6));
                pd.CustSumm = Convert.ToDouble(dr.GetValue(7));
                pd.PurchSumm = Convert.ToDouble(dr.GetValue(8));
                pd.EndSumm = Convert.ToDouble(dr.GetValue(9));
                pd.Date = Convert.ToDateTime(dr.GetValue(10));
                pd.UserId = Convert.ToInt32(dr.GetValue(11));
            }
            return pd;
        }

        public static void InsertPurchDocument(PurchDocument pd)
        {
            DataTable dt = new DataTable();
            OleDbConnection connect = new OleDbConnection(ConfigurationManager.ConnectionStrings["base"].ConnectionString);
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "insert into PurchDocument (ProvidersDocumentNumber," +
                                  "Number," +
                                  "DocumentDate," +
                                  "RegDate," +
                                  "PriceConditionID," +
                                  "ProvidersID," +
                                  "CustSumm" +
                                  "PurchSumm" +
                                  "EndSumm" +
                                  "Date" +
                                  "UsersID" +
                                  " values(" +
                                  "@Number," +
                                  "@DocumentDate," +
                                  "@RegDate," +
                                  "@PriceConditionID," +
                                  "@ProvidersID," +
                                  "@CustSumm" +
                                  "@PurchSumm" +
                                  "@EndSumm" +
                                  "@Date" +
                                  "@UsersID)";
            command.Parameters.AddWithValue("@Number", pd.Number);
            command.Parameters.AddWithValue("@DocumentDate", pd.DocumentDate);
            command.Parameters.AddWithValue("@RegDate", pd.RegDate);
            command.Parameters.AddWithValue("@PriceConditionID", pd.PriceConditionID);
            command.Parameters.AddWithValue("@ProvidersID", pd.ProvidersID);
            command.Parameters.AddWithValue("@CustSumm", pd.CustSumm);
            command.Parameters.AddWithValue("@PurchSumm", pd.PurchSumm);
            command.Parameters.AddWithValue("@EndSumm", pd.EndSumm);
            command.Parameters.AddWithValue("@Date", pd.Date);
            command.Parameters.AddWithValue("@UsersID", pd.UserId);
            connect.Open();
            command.ExecuteNonQuery();
            connect.Close();
        }

        public static void UpdatePurchDocument(PurchDocument pd)
        {
            DataTable dt = new DataTable();
            OleDbConnection connect = new OleDbConnection(ConfigurationManager.ConnectionStrings["base"].ConnectionString);
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "Update PurchDocument set ProvidersDocumentNumber=@ProvidersDocumentNumber," +
                                  "Number=@Number," +
                                  "DocumentDate=@DocumentDate," +
                                  "RegDate=@RegDate," +
                                  "PriceConditionID=@PriceConditionID," +
                                  "ProvidersID=@ProvidersID," +
                                  "CustSumm=@CustSumm" +
                                  "PurchSumm=@PurchSumm" +
                                  "EndSumm=@EndSumm" +
                                  "Date=now()" +
                                  "UsersID=@UsersID";
            command.Parameters.AddWithValue("@Number", pd.Number);
            command.Parameters.AddWithValue("@DocumentDate", pd.DocumentDate);
            command.Parameters.AddWithValue("@RegDate", pd.RegDate);
            command.Parameters.AddWithValue("@PriceConditionID", pd.PriceConditionID);
            command.Parameters.AddWithValue("@ProvidersID", pd.ProvidersID);
            command.Parameters.AddWithValue("@CustSumm", pd.CustSumm);
            command.Parameters.AddWithValue("@PurchSumm", pd.PurchSumm);
            command.Parameters.AddWithValue("@EndSumm", pd.EndSumm);
            command.Parameters.AddWithValue("@Date", pd.Date);
            command.Parameters.AddWithValue("@UsersID", pd.UserId);
            connect.Open();
            command.ExecuteNonQuery();
            connect.Close();
        }

        public static void RemoveDocumentByID(int id)
        {
            DataTable dt = new DataTable();
            OleDbConnection connect = new OleDbConnection(ConfigurationManager.ConnectionStrings["base"].ConnectionString);
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "delete from RemoveDocument where id=@id";
            command.Parameters.AddWithValue("@id", id);
            connect.Open();
            command.ExecuteNonQuery();
            connect.Close();
        }
    }
}
